Computer systems typically comprise a combination of computer programs and hardware, such as semiconductors, transistors, chips, circuit boards, storage devices, and processors. The computer programs are stored in the storage devices and are executed by the processors. Fundamentally, computer systems are used for the storage, manipulation, and analysis of data.
One mechanism for managing data is called a database management system (DBMS) or simply a database. Many different types of databases are known, but the most common is usually called a relational database (RDB), which organizes data in tables that have rows, which represent individual entries, tuples, or records in the database, and columns, fields, or attributes, which define what is stored in each entry, tuple, or record. Each table has a unique name within the database and each column has a unique name within the particular table. The database also has one or more indexes, which are data structures that inform the DBMS of the location of a certain row in a table given an indexed column value, analogous to a book index informing the reader on which page a given word appears.
The most common way to retrieve data from a database is through statements called database queries, which may originate from user interfaces, application programs, or remote computer systems, such as clients or peers. A query is an expression evaluated by the DBMS, in order to retrieve data from the database that satisfies or meets the criteria or conditions specified in the query. Although the query requires the return of a particular data set in response, the method of query execution is typically not specified by the query. Thus, after the DBMS receives a query, the DBMS interprets the query and determines what internal steps are necessary to satisfy the query. These internal steps may comprise an identification of the table or tables specified in the query, the row or rows selected in the query, and other information such as whether to use an existing index, whether to build a temporary index, whether to use a temporary file to execute a sort, and/or the order in which the tables are to be joined together to satisfy the query. When taken together, these internal steps are referred to as an execution plan. The DBMS often saves the execution plan and reuses it when the user or requesting program repeats the query, which is a common occurrence, instead of undergoing the time-consuming process of recreating the execution plan.
Many different execution plans may be created for any one query, each of which would return the same data set that satisfies the query, yet the different execution plans may provide widely different performance. Thus, the execution plan selected by the DBMS needs to provide the required data at a reasonable cost in terms of time and hardware resources. Hence, the DBMS often creates multiple prospective execution plans and then chooses the best, fastest, or least expensive one, to execute. One factor that contributes to the cost of a particular execution plan is the number of rows that the execution plan, when executed, returns from the database tables. One important aspect that influences the number of rows processed is the join order of the tables. In response to a query that requests data from multiple tables, the DBMS joins rows from these multiple tables (the rows are often concatenated horizontally in a result set), in order to find and retrieve the data from all the tables. Thus, a join operation is a relationship between two tables accessed by a query (a join query), and a join operation is performed to connect (or join) data from two or more tables, wherein the DBMS joins rows with particular attributes together to form a new row. The join order is typically specified by the execution plan and is the order in which the DBMS performs join operations when the DBMS executes the query via the execution plan to retrieve and join rows of data from the database tables into the result set.
Join operations are typically implemented using a nested loop algorithm, where the resultant new rows from the first two tables in the join order are joined to the resultant rows from the third table, and those results are joined to the fourth table, etc. Eventually all of the needed join operations are complete, and the resultant new rows are the result set that satisfies the query. Because a single join is limited to accessing two tables, multi-table joins are performed in sequence according to a particular order. Many different join queries may be implemented by joining the tables in any of several possible join orders. For example, a query that involves joining tables A, B, and C can often be performed as a join of tables A and B followed by a join of the results of table A joined to table B and table C. Alternatively, in many instances, the same query can be performed as a join of tables A and C followed by the join of the results of table A joined to table C and table B. The DBMS attempts to select a join order for an execution plan that will eliminate the greatest number of rows from the potential result set early in the join processing, which saves the costs associated with repeatedly accessing tables later in the join operation.
Another factor that contributes to the cost of a particular execution plan is whether the rows read from the table are stored in memory or in a secondary storage device. The tables typically permanently reside on secondary storage devices, such as disk drives and are read into a smaller and faster memory, as needed. An execution plan may specify a table scan, which when executed may retrieve all pages from the secondary storage units to memory for processing. The secondary storage Input/Output operation time is often a major component of the processing time for a table scan.